package org.i_plus.model.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.i_plus.model.MemberBean;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class MemberDAOHibernate {
	private SessionFactory sessionFactory;
	private MemberBean bean;
	public void setSessionFactory(SessionFactory sessionFactory)
		{
			this.sessionFactory = sessionFactory;
		}
	public MemberBean select(){
		
		Session ses=this.sessionFactory.getCurrentSession();
		Query query= ses.createQuery("from MemberBean");
		List<MemberBean> list=query.list();
//		Criteria cri=ses.createCriteria(MemberBean.class);
//		Iterator use=cri.list().iterator();
//		while(use.hasNext())
		for(MemberBean bean:list)
		{
			//MemberBean bean=(MemberBean)use.next();
			System.out.print(bean.getAccount()+" ");
			System.out.print(bean.getPwd()+" ");
			System.out.print(bean.getName()+" ");
			System.out.print(bean.getNickname()+" ");
			System.out.print(bean.getPic1()+" ");
			System.out.print(bean.getPic2()+" ");
			System.out.print(bean.getSex()+" ");
			System.out.print(bean.getBdate()+" ");
			System.out.print(bean.getHight()+" ");
			System.out.print(bean.getRegion()+" ");
			System.out.print(bean.getBlood()+" ");
			System.out.print(bean.getState()+" ");
			System.out.print(bean.getPop()+" ");
			System.out.print(bean.getBonus()+" ");
			System.out.print(bean.getHobby()+" ");
			System.out.print(bean.getMlike()+" ");
			System.out.print(bean.getPlay()+" ");
			System.out.print(bean.getTarot()+" ");
			System.out.print(bean.getMkind()+" ");
			System.out.println(bean.getCheckvip());
		}
		//ses.close();
		return bean;
		}
	public MemberBean Select_By_Account(String account)
	{
		Session ses=this.sessionFactory.getCurrentSession();
		MemberBean bean= (MemberBean)ses.get(MemberBean.class, account);
		//Transaction tx=ses.beginTransaction();
//		Criteria cri=ses.createCriteria(MemberBean.class);
//		cri.add(Restrictions.eq("Account", account));
//		Iterator use=cri.list().iterator();
//		while(use.hasNext())
		//{
			//MemberBean bean=(MemberBean)ses.get(MemberBean.class, account);
			//MemberBean bean=(MemberBean)use.next();
//			System.out.print(bean.getAccount()+" ");
//			System.out.print(bean.getPwd()+" ");
//			System.out.print(bean.getName()+" ");
//			System.out.print(bean.getNickname()+" ");
//			System.out.print(bean.getPic1()+" ");
//			System.out.print(bean.getPic2()+" ");
//			System.out.print(bean.getSex()+" ");
//			System.out.print(bean.getBdate()+" ");
//			System.out.print(bean.getHight()+" ");
//			System.out.print(bean.getRegion()+" ");
//			System.out.print(bean.getBlood()+" ");
//			System.out.print(bean.getState()+" ");
//			System.out.print(bean.getPop()+" ");
//			System.out.print(bean.getBonus()+" ");
//			System.out.print(bean.getHobby()+" ");
//			System.out.print(bean.getMlike()+" ");
//			System.out.print(bean.getPlay()+" ");
//			System.out.print(bean.getTarot()+" ");
//			System.out.print(bean.getMkind()+" ");
//			System.out.println(bean.getCheckvip());
			return bean;
		}
		//ses.close();
		//DefaultFactory.shutdown();
		//}
	public void insert(MemberBean bean){
		Session ses=this.sessionFactory.getCurrentSession();
			MemberBean mb=new MemberBean();
			mb.setAccount(bean.getAccount());
			mb.setPwd(bean.getPwd());
			mb.setName(bean.getName());
			mb.setNickname(bean.getNickname());
			mb.setPic1(bean.getPic1());
			mb.setPic2(bean.getPic2());
			mb.setSex(bean.getSex());
			mb.setBdate(bean.getBdate());
			mb.setHight(bean.getHight());
			mb.setRegion(bean.getRegion());
			mb.setBlood(bean.getBlood());
			mb.setState(bean.getState());
			mb.setPop(bean.getPop());
			mb.setBonus(bean.getBonus());
			mb.setHobby(bean.getHobby());
			mb.setMlike(bean.getMlike());
			mb.setPlay(bean.getPlay());
			mb.setTarot(bean.getTarot());
			mb.setMkind(bean.getMkind());
			mb.setCheckvip(bean.getCheckvip());
			ses.save(mb);
		}
	public void delete(String account)
	{
		Session ses=this.sessionFactory.getCurrentSession();
		Query query=ses.createQuery("delete from MemberBean where account=?");
		query.setParameter(0, account);
		query.executeUpdate();
		
	}
	public void Update(String account,byte[] pwd,String name,String nickname,byte[] pic1,
			byte[] pic2,String sex,java.sql.Date bdate,int hight,String region,String blood,
			String state,int pop,int bonus,String hobby,int mlike,boolean play,int tarot,
			String mkind,java.sql.Date checkvip){
		Session ses=this.sessionFactory.getCurrentSession();
		MemberBean mb=(MemberBean)ses.get(MemberBean.class, account);
			mb.setPwd(pwd);
			mb.setName(name);
			mb.setNickname(nickname);
			mb.setPic1(pic1);
			mb.setPic2(pic2);
			mb.setSex(sex);
			mb.setBdate(bdate);
			mb.setHight(hight);
			mb.setRegion(region);
			mb.setBlood(blood);
			mb.setState(state);
			mb.setPop(pop);
			mb.setBonus(bonus);
			mb.setHobby(hobby);
			mb.setMlike(mlike);
			mb.setPlay(play);
			mb.setTarot(tarot);
			mb.setMkind(mkind);
			mb.setCheckvip(checkvip);
			ses.update(mb);		
	}
	public void Updateall(MemberBean bean){
		Session ses=this.sessionFactory.getCurrentSession();
		MemberBean mb=(MemberBean)ses.get(MemberBean.class, bean.getAccount());
			//mb.setPwd(bean.getPwd());
			mb.setName(bean.getName());
			mb.setNickname(bean.getNickname());
			mb.setPic1(bean.getPic1());
			mb.setPic2(bean.getPic2());
			mb.setSex(bean.getSex());
			mb.setBdate(bean.getBdate());
			mb.setHight(bean.getHight());
			mb.setRegion(bean.getRegion());
			mb.setBlood(bean.getBlood());
			mb.setState(bean.getState());
			mb.setPop(bean.getPop());
			mb.setBonus(bean.getBonus());
			mb.setHobby(bean.getHobby());
			mb.setMlike(bean.getMlike());
			mb.setPlay(bean.getPlay());
			mb.setTarot(bean.getTarot());
			mb.setMkind(bean.getMkind());
			mb.setCheckvip(bean.getCheckvip());
			ses.update(mb);
		
	}
	public List<MemberBean> findMany(int page,String hql) {
		Session session=this.sessionFactory.getCurrentSession();
		Query query = session.createQuery(hql);
		query.setFirstResult((page-1)*10);
		query.setMaxResults(10);     
        return query.list();
	}
	//page region sex hight blood mstate play ball run reading swim movie singing bike tvgame
		public int callStoredProcedureMatchPages(String region,String sex,int hight,
																								  String blood,String mstate,boolean play,
																								  boolean ball,boolean run,boolean reading,
																								  boolean swim,boolean movie,
																								  boolean singing,boolean bike,
																								  boolean tvgame,String constellation
																									){
			final String URL = "jdbc:sqlserver://localhost:1433;database=LovePlusPlus";
			final String USERNAME = "sa";
			final String PASSWORD = "passw0rd";
			int result =1;
			Connection conn = null;
			CallableStatement cstm = null;
			ResultSet rs = null;
			try {
			conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
			cstm = conn.prepareCall("{call match_pages (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
			cstm.setString(1, region);
			cstm.setString(2, sex);
			cstm.setInt(3, hight);
			cstm.setString(4, blood);
			cstm.setString(5, mstate);
			cstm.setBoolean(6, play);
			cstm.setBoolean(7, ball);
			cstm.setBoolean(8, run);
			cstm.setBoolean(9, reading);
			cstm.setBoolean(10, swim);
			cstm.setBoolean(11, movie);
			cstm.setBoolean(12, singing);
			cstm.setBoolean(13, bike);
			cstm.setBoolean(14, tvgame);
			cstm.setString(15, constellation);
			rs = cstm.executeQuery();
			rs.next();
			result = rs.getInt(1);
			} catch (SQLException e) {
			 e.printStackTrace();
			} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (cstm != null) {
				try {
					cstm.close();
				} catch (SQLException se) {
					se.printStackTrace(System.err);
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (Exception e) {
					e.printStackTrace(System.err);
				}
			}
			}
		return result;		
		}
		
		public List<MemberBean> callStoredProcedureMatch(int page,String region,String sex,int hight,
				  String blood,String mstate,boolean play,
				  boolean ball,boolean run,boolean reading,
				  boolean swim,boolean movie,
				  boolean singing,boolean bike,
				  boolean tvgame,String constellation
					){
				  final String URL = "jdbc:sqlserver://localhost:1433;database=LovePlusPlus";
				  final String USERNAME = "sa";
				  final String PASSWORD = "passw0rd";
				  List<MemberBean> result = new ArrayList<MemberBean>();
				  Connection conn = null;
				  CallableStatement cstm = null;
				  ResultSet rs = null;
				  try {
					  conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
					  cstm = conn.prepareCall("{call match (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
					  cstm.setInt(1, page);
					  cstm.setString(2, region);
					  cstm.setString(3, sex);
					  cstm.setInt(4, hight);
					  cstm.setString(5, blood);
					  cstm.setString(6, mstate);
					  cstm.setBoolean(7, play);
					  cstm.setBoolean(8, ball);
					  cstm.setBoolean(9, run);
					  cstm.setBoolean(10, reading);
					  cstm.setBoolean(11, swim);
					  cstm.setBoolean(12, movie);
					  cstm.setBoolean(13, singing);
					  cstm.setBoolean(14, bike);
					  cstm.setBoolean(15, tvgame);
					  cstm.setString(16, constellation);
					  rs = cstm.executeQuery();
					  while(rs.next()){
						  MemberBean bean = new MemberBean();
						  bean.setAccount(rs.getString(1));
						  bean.setName(rs.getString(2));
						  bean.setNickname(rs.getString(3));
						  bean.setPic1(rs.getBytes(4));
						  bean.setBdate(rs.getDate(5));
						  bean.setSex(rs.getString(6));
						  bean.setBlood(rs.getString(7));
						  bean.setHight(rs.getInt(8));
						  bean.setRegion(rs.getString(9));
						  result.add(bean);
					  }
				  } catch (SQLException e) {
					  e.printStackTrace();
					  result = null;
				  } finally {
					  if (rs != null) {
						  try {
							  rs.close();
						  } catch (SQLException se) {
							  se.printStackTrace(System.err);
						  }
					  }
					  if (cstm != null) {
						  try {
							  cstm.close();
						  } catch (SQLException se) {
							  se.printStackTrace(System.err);
						  }
					  }
					  if (conn != null) {
						  try {
							  conn.close();
						  } catch (Exception e) {
							  e.printStackTrace(System.err);
						  }
					  }
				  }
				  return result;		
		}
	
	public static void main(String[] args) {
		//MemberDAOHibernate mh=new MemberDAOHibernate();
		
		
		ApplicationContext context = new ClassPathXmlApplicationContext("beans.config.xml");
		MemberDAOHibernate mh = (MemberDAOHibernate)context.getBean("MemberDAO");
		//mh.Select_By_Account("tiger");
		mh.sessionFactory.getCurrentSession().beginTransaction();
		//mh.select();
		mh.Select_By_Account("lion");
		//SimpleDateFormat fomat = new SimpleDateFormat("dd-MM-yyyy");
		//String pwd1="az";
		//byte[] pwdd=pwd1.getBytes();
		//mh.insert("bird", pwdd,"fox" , "Bolf", null, null,"S", java.sql.Date.valueOf("1984-1-1"), 168, "tw", "a", "bc", 123, 100, "dd", "ff", true, 2, "f",java.sql.Date.valueOf("2014-1-5"));
		//mh.delete("cat");
//		List<MemberBean> beans = mh.callStoredProcedureMatch(1, null, null, 0, null, null, false, false, false, false, false, false, false, false, false,"魔羯");
//		for(MemberBean bean:beans){
//			System.out.println(bean.getAccount());
//			System.out.println(bean.getName());
//			System.out.println(bean.getNickname());
//			System.out.println(bean.getPic1());
//			System.out.println(bean.getBdate());
//			System.out.println(bean.getSex());
//			System.out.println(bean.getBlood());
//			System.out.println(bean.getState());
//			System.out.println(bean.getRegion());
//		}
//		int page = mh.callStoredProcedureMatchPages(null, null, 0, null, null, false, false, false, false, false, false, false, false, false,"魔羯");
//		System.out.println(page);
		
		mh.sessionFactory.getCurrentSession().getTransaction().commit();	
		}
	}

